package com.union.core.information.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.information.dao.KnowledgeManageDao;
import com.union.core.information.model.KnowledgeManage;
import com.union.core.sys.dao.impl.RoleDaoImpl;
import com.union.core.sys.model.Role;
/**
 * 接口实现: 知识管理
 * filename: KnowledgeManageDaoImpl.java
 * project:  union
 * @company Hainan Jowe Technology Ltd
 * @author Harry Zhang
 * @create in 2012-11-05
 * @version 1.0
 */
public class KnowledgeManageDaoImpl implements KnowledgeManageDao {
	DBOperation opera;
	private static Logger log = Logger.getLogger(KnowledgeManageDaoImpl.class.getName());
	public boolean addKnowledge(KnowledgeManage knowledgeManage) {
			boolean bl=false;
			try{
				opera=new DBOperation();
				opera.transcationBegin();
				String sql="insert into knowledge_manage (knowledge_title,knowledge_content,release_name" +
						",release_time,organization_id,person_id ,organization_name) values(?,?,?,SYSDATE(),?,?,?)";
				opera.prepStatment(sql);
				opera.setString(1, knowledgeManage.getKnowledge_title());
				opera.setString(2, knowledgeManage.getKnowledge_content());
				opera.setString(3, knowledgeManage.getRelease_name());
				opera.setInt(4, knowledgeManage.getOrganization_id());
				opera.setInt(5, knowledgeManage.getPerson_id());
				opera.setString(6, knowledgeManage.getOrganization_name());
				opera.execute();
				bl=opera.transcationCommit();
			}catch(Exception e){
				log.error(e);
			}finally{
				opera.closePres();
				opera.closeConn();
			}
		return bl;
	}

	public boolean delKnowledge(int id) {
		boolean bl=false;
		try{
			opera=new DBOperation();
			opera.transcationBegin();
			String sql="delete from knowledge_manage where id=?";
			opera.prepStatment(sql);
			opera.setInt(1, id);
			opera.execute();
			bl=opera.transcationCommit();
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return bl;
	}

	public boolean editKnowledge(KnowledgeManage knowledgeManage) {
		boolean bl=false;
		try{
			opera=new DBOperation();
			opera.transcationBegin();
			String sql="update knowledge_manage set knowledge_title=?,knowledge_content=?," +
					"edit_name=?,edit_time=SYSDATE() where id=?";
			opera.prepStatment(sql);
			opera.setString(1, knowledgeManage.getKnowledge_title());
			opera.setString(2, knowledgeManage.getKnowledge_content());
			opera.setString(3, knowledgeManage.getEdit_name());
			opera.setInt(4, knowledgeManage.getId());
			opera.executeUpdate();
			bl=opera.transcationCommit();
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return bl;
	}

	public List<KnowledgeManage> queryList(int id, String knowledgeTitle,
			String knowledgeContent, String releaseName, String releaseTime,
			int organizationId, int personId, String organizationName,String startDate,String endDate,
			String paraName, String paraVal, String orderBy, int start,
			int pagecount) {
		opera=new DBOperation();
		List<KnowledgeManage> knowledgeManageList=new ArrayList<KnowledgeManage>();
		String sql1="";
		String sql2="";
		String sql3="";
		String sql4="";
		String sql5="";
		String sql6="";
		String sql7="";
		String sql8="";
		String sql9="";
		String sql10="";
		String order="";
		if (startDate != null && endDate != null){
			sql1 = " and release_time between DATE_FORMAT('"+startDate+"','%Y-%m-%d') and DATE_FORMAT('"+endDate+"','%Y-%m-%d') ";
		}
		
		if (id!=0) sql2 = " and id="+id;
		if (knowledgeTitle!=null) sql3 = " and knowledge_title='"+knowledgeTitle+"'";
		if (knowledgeContent!=null) sql4 = " and knowledge_content='"+knowledgeContent+"'";
		if (releaseName!=null) sql5 = " and release_name='"+releaseName+"'";
		if (releaseTime!=null) sql6 = " and release_time='"+releaseTime+"'";
		if (organizationId!=0) sql7 = " and organization_id="+organizationId;
		if (personId!=0) sql8 = " and person_id="+personId;
		if (organizationName!=null) sql9 = " and organization_name='"+organizationName+"'";
		
		
		if (paraName != null && paraVal != null) {
			if(paraName.equals("id")) sql10 = " and id = "+paraVal;
			if(paraName.equals("knowledge_title")) sql10 = " and knowledge_title like '%"+paraVal+"%'";
			if(paraName.equals("knowledge_content")) sql10 = " and knowledge_content like '%"+paraVal+"%'";
			if(paraName.equals("release_name")) sql10 = " and release_name like '%"+paraVal+"%'";
			if(paraName.equals("release_time")) sql10 = " and release_time like '%"+paraVal+"%'";
			if(paraName.equals("organization_id")) sql10 = " and organization_id = "+paraVal;
			if(paraName.equals("person_id")) sql10 = " and person_id = "+paraVal;
			if(paraName.equals("organization_name")) sql10 = " and organization_name like '%"+paraVal+"%'";
		}

		if (orderBy != null&& !"".equals(orderBy)) {
			String str[] = orderBy.split("-");			
			if (str[1].equals("UP"))
				order = " order by " + str[0];
			if (str[1].equals("DOWN"))
				order = " order by " + str[0] + " desc";
		} else {
			order = " order by id asc";
		}
		
		try{
		opera.transcationBegin();

		String sql="select id,knowledge_title,knowledge_content,release_name,release_time," +
				"organization_id,person_id ,organization_name,edit_name,edit_time" +
				" from knowledge_manage where id>0"
			+sql1+sql2+sql3+sql4+sql5+sql6+sql7+sql8+sql9+sql10+order+" limit ?,?";
		opera.prepStatment(sql);
		opera.setInt(1, start);
		opera.setInt(2, pagecount);
		ResultSet rs=opera.executeQuery();
		
		while(rs.next()){
			KnowledgeManage knowledgeManage=new KnowledgeManage();
			knowledgeManage.setId(rs.getInt(1));
			knowledgeManage.setKnowledge_title(rs.getString(2));
			knowledgeManage.setKnowledge_content(rs.getString(3));
			knowledgeManage.setRelease_name(rs.getString(4));
			knowledgeManage.setRelease_time(rs.getString(5));
			knowledgeManage.setOrganization_id(rs.getInt(6));
			knowledgeManage.setPerson_id(rs.getInt(7));
			knowledgeManage.setOrganization_name(rs.getString(8));
			knowledgeManage.setEdit_name(rs.getString(9));
			knowledgeManage.setEdit_time(rs.getString(10));
			knowledgeManageList.add(knowledgeManage);
		}
		
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return knowledgeManageList;
	}

	public int queryListCount(int id, String knowledgeTitle,
			String knowledgeContent, String releaseName, String releaseTime,
			int organizationId, int personId, String organizationName,String startDate,String endDate,
			String paraName, String paraVal) {
		int count=0;
		opera=new DBOperation();
		String sql1="";
		String sql2="";
		String sql3="";
		String sql4="";
		String sql5="";
		String sql6="";
		String sql7="";
		String sql8="";
		String sql9="";
		String sql10="";
		if (startDate != null && endDate != null){
			sql1 = " and release_time between DATE_FORMAT('"+startDate+"','%Y-%m-%d') and DATE_FORMAT('"+endDate+"','%Y-%m-%d') ";
		}
		
		if (id!=0) sql2 = " and id="+id;
		if (knowledgeTitle!=null) sql3 = " and knowledge_title='"+knowledgeTitle+"'";
		if (knowledgeContent!=null) sql4 = " and knowledge_content='"+knowledgeContent+"'";
		if (releaseName!=null) sql5 = " and release_name='"+releaseName+"'";
		if (releaseTime!=null) sql6 = " and release_time='"+releaseTime+"'";
		if (organizationId!=0) sql7 = " and organization_id="+organizationId;
		if (personId!=0) sql8 = " and person_id="+personId;
		if (organizationName!=null) sql9 = " and organization_name='"+organizationName+"'";
		
		
		if (paraName != null && paraVal != null) {
			if(paraName.equals("id")) sql10 = " and id = "+paraVal;
			if(paraName.equals("knowledge_title")) sql10 = " and knowledge_title like '%"+paraVal+"%'";
			if(paraName.equals("knowledge_content")) sql10 = " and knowledge_content like '%"+paraVal+"%'";
			if(paraName.equals("release_name")) sql10 = " and release_name like '%"+paraVal+"%'";
			if(paraName.equals("release_time")) sql10 = " and release_time like '%"+paraVal+"%'";
			if(paraName.equals("organization_id")) sql10 = " and organization_id = "+paraVal;
			if(paraName.equals("person_id")) sql10 = " and person_id = "+paraVal;
			if(paraName.equals("organization_name")) sql10 = " and organization_name like '%"+paraVal+"%'";
		}
		
		try{
		opera.transcationBegin();
		String sql="select count(*) from knowledge_manage where id>0"
			+sql1+sql2+sql3+sql4+sql5+sql6+sql7+sql8+sql9+sql10;
		opera.prepStatment(sql);
		ResultSet rs=opera.executeQuery();
		if(rs.next()){
			count=rs.getInt(1);
		}
		
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return count;
	}

	public KnowledgeManage queryById(int id) {
		opera=new DBOperation();
		KnowledgeManage knowledgeManage=new KnowledgeManage();
		try{
		opera.transcationBegin();
		String sql="select id,knowledge_title,knowledge_content,release_name,release_time," +
				"organization_id,person_id ,organization_name,edit_name,edit_time" +
				" from knowledge_manage where id="+id;
		opera.prepStatment(sql);
		ResultSet rs=opera.executeQuery();
		
		if(rs.next()){
			
			knowledgeManage.setId(rs.getInt(1));
			knowledgeManage.setKnowledge_title(rs.getString(2));
			knowledgeManage.setKnowledge_content(rs.getString(3));
			knowledgeManage.setRelease_name(rs.getString(4));
			knowledgeManage.setRelease_time(rs.getString(5));
			knowledgeManage.setOrganization_id(rs.getInt(6));
			knowledgeManage.setPerson_id(rs.getInt(7));
			knowledgeManage.setOrganization_name(rs.getString(8));
			knowledgeManage.setEdit_name(rs.getString(9));
			knowledgeManage.setEdit_time(rs.getString(10));
		}
		
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return knowledgeManage;
	}

}
